﻿-- Using UserName as provider key (default)
CREATE TABLE UsersByUserName (
	UserName				nvarchar(100)		NOT NULL,
	PasswordHash			binary(64)			NOT NULL,
	PasswordSalt			binary(128)			NOT NULL,
	Email					nvarchar(max)		NOT NULL,
	Comment					nvarchar(max)		NULL,
	IsApproved				bit					NOT NULL,
	DateCreated				datetime			NOT NULL,
	DateLastLogin			datetime			NULL,
	DateLastActivity		datetime			NULL,
	DateLastPasswordChange	datetime			NOT NULL,
	CONSTRAINT PK_UsersByUserName PRIMARY KEY CLUSTERED (UserName)
)

-- Using IntIdentity as provider key
CREATE TABLE UsersByIntIdentity (
	UserId					int identity		NOT NULL,
	UserName				nvarchar(100)		NOT NULL,
	PasswordHash			binary(64)			NOT NULL,
	PasswordSalt			binary(128)			NOT NULL,
	Email					nvarchar(max)		NOT NULL,
	Comment					nvarchar(max)		NULL,
	IsApproved				bit					NOT NULL,
	DateCreated				datetime			NOT NULL,
	DateLastLogin			datetime			NULL,
	DateLastActivity		datetime			NULL,
	DateLastPasswordChange	datetime			NOT NULL,
	CONSTRAINT PK_UsersByIntIdentity PRIMARY KEY CLUSTERED (UserId)
)
CREATE UNIQUE NONCLUSTERED INDEX IX_UsersByIntIdentity_UserName ON UsersByIntIdentity (UserName)

-- Using Guid as provider key
CREATE TABLE UsersByGuid (
	UserId					uniqueidentifier	NOT NULL,
	UserName				nvarchar(100)		NOT NULL,
	PasswordHash			binary(64)			NOT NULL,
	PasswordSalt			binary(128)			NOT NULL,
	Email					nvarchar(max)		NOT NULL,
	Comment					nvarchar(max)		NULL,
	IsApproved				bit					NOT NULL,
	DateCreated				datetime			NOT NULL,
	DateLastLogin			datetime			NULL,
	DateLastActivity		datetime			NULL,
	DateLastPasswordChange	datetime			NOT NULL,
	CONSTRAINT PK_UsersByGuid PRIMARY KEY CLUSTERED (UserId)
)
CREATE UNIQUE NONCLUSTERED INDEX IX_UsersByGuid_UserName ON UsersByGuid (UserName)
